/* ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- */
/* 
	Creation of objects to support Sueetie.Core and User Avatars
	Released 3/20/09
	Version 1.0
	
	Processes: 

		CREATE TABLE [dbo].[Sueetie_Users]
		CREATE TABLE [dbo].[Sueetie_UserAvatar]
				
		CREATE FUNCTION [dbo].[Sueetie_fn_GetElement]
		CREATE FUNCTION [dbo].[Sueetie_fn_GetProfileElement]
	
		CREATE VIEW [dbo].[Sueetie_vw_user]
		CREATE VIEW [dbo].[sueetie_vw_UserProfileData]
		
		CREATE PROCEDURE [dbo].[sueetie_User_Create]
		CREATE PROCEDURE [dbo].[sueetie_User_getByID] 
		CREATE PROCEDURE [dbo].[sueetie_User_GetProfileDetails]
		CREATE PROCEDURE [dbo].[sueetie_User_SaveAvatar]
		
		POPULATE Sueetie_Users and Sueetie_UserAvatar with existing member data
		
*/
/* ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- */

set nocount on

print 'CREATE TABLE [dbo].[Sueetie_Users]'

/* -- CREATE TABLE [dbo].[Sueetie_Users] ------------------------------------------------------------------------------------------------------------------------------- */

/****** Object:  Table [dbo].[Sueetie_Users]    Script Date: 03/20/2009 10:23:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Sueetie_Users](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[MembershipID] [uniqueidentifier] NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[Email] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Sueetie_Users] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


print 'CREATE TABLE [dbo].[Sueetie_UserAvatar]'

/* -- CREATE TABLE [dbo].[Sueetie_UserAvatar] ------------------------------------------------------------------------------------------------------------------------------- */

/****** Object:  Table [dbo].[Sueetie_UserAvatar]    Script Date: 03/20/2009 10:23:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Sueetie_UserAvatar](
	[UserID] [int] NOT NULL,
	[AvatarImage] [image] NULL,
	[AvatarImageType] [nvarchar](50) NULL,
 CONSTRAINT [PK_Sueetie_UserAvatar] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



print 'CREATE FUNCTION [dbo].[Sueetie_fn_GetElement]'

/* -- CREATE FUNCTION [dbo].[Sueetie_fn_GetElement]  ------------------------------------------------------------------------------------------------------------------------------- */


/****** Object:  UserDefinedFunction [dbo].[Sueetie_fn_GetElement]    Script Date: 03/20/2009 10:35:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Sueetie_fn_GetElement]
(
@ord AS INT,
@str AS VARCHAR(8000),
@delim AS VARCHAR(1) )
RETURNS INT
AS
BEGIN
  -- If input is invalid, return null.
  IF  @str IS NULL
      OR LEN(@str) = 0
      OR @ord IS NULL
      OR @ord < 1
      -- @ord > [is the] expression that calculates the number of elements.
      OR @ord > LEN(@str) - LEN(REPLACE(@str, @delim, '')) + 1
    RETURN NULL
  DECLARE @pos AS INT, @curord AS INT
  SELECT @pos = 1, @curord = 1
  -- Find next element's start position and increment index.
  WHILE @curord < @ord
    SELECT
      @pos    = CHARINDEX(@delim, @str, @pos) + 1,
      @curord = @curord + 1
  RETURN    CAST(SUBSTRING(@str, @pos, CHARINDEX(@delim, @str + @delim, @pos) - @pos) AS INT)
END
GO


print 'CREATE FUNCTION [dbo].[Sueetie_fn_GetProfileElement]'

/* -- CREATE FUNCTION [dbo].[Sueetie_fn_GetProfileElement]  ------------------------------------------------------------------------------------------------------------------------------- */


/****** Object:  UserDefinedFunction [dbo].[Sueetie_fn_GetProfileElement]    Script Date: 03/20/2009 10:35:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[Sueetie_fn_GetProfileElement]
(
@fieldName AS NVARCHAR(100),
@fields AS NVARCHAR(4000),
@values AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN

  -- If input is invalid, return null.
  IF  @fieldName IS NULL
      OR LEN(@fieldName) = 0
      OR @fields IS NULL
      OR LEN(@fields) = 0
      OR @values IS NULL
      OR LEN(@values) = 0
    RETURN NULL

-- locate FieldName in Fields
DECLARE @fieldNameToken AS NVARCHAR(20)
DECLARE @fieldNameStart AS INTEGER, @valueStart AS INTEGER, @valueLength AS INTEGER

-- Only handle string type fields (:S:)
SET @fieldNameStart = CHARINDEX(@fieldName + ':S',@Fields,0)

-- If field is not found, return null
IF @fieldNameStart = 0 RETURN NULL
SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 3

-- Get the field token which I've defined as the start of the field offset to the end of the length
SET @fieldNameToken = SUBSTRING(@Fields,@fieldNameStart,LEN(@Fields)-@fieldNameStart)

-- Get the values for the offset and length
SET @valueStart = dbo.sueetie_fn_getelement(1,@fieldNameToken,':')
SET @valueLength = dbo.sueetie_fn_getelement(2,@fieldNameToken,':')

-- Check for sane values, 0 length means the profile item was stored, just no data
IF @valueLength = 0 RETURN ''

-- Return the string
RETURN SUBSTRING(@values, @valueStart+1, @valueLength)
END
GO



print 'CREATE VIEW [dbo].[Sueetie_vw_user]'

/* -- CREATE VIEW [dbo].[Sueetie_vw_user]  ------------------------------------------------------------------------------------------------------------------------------- */


/****** Object:  View [dbo].[Sueetie_vw_user]    Script Date: 03/20/2009 10:32:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[Sueetie_vw_user]
AS
SELECT     dbo.Sueetie_Users.UserID, dbo.Sueetie_Users.MembershipID, dbo.Sueetie_Users.UserName, dbo.Sueetie_Users.Email, dbo.Sueetie_UserAvatar.AvatarImage, 
                      dbo.Sueetie_UserAvatar.AvatarImageType
FROM         dbo.Sueetie_Users INNER JOIN
                      dbo.Sueetie_UserAvatar ON dbo.Sueetie_Users.UserID = dbo.Sueetie_UserAvatar.UserID INNER JOIN
                      dbo.aspnet_Users ON dbo.Sueetie_Users.MembershipID = dbo.aspnet_Users.UserId

GO


print 'CREATE VIEW [dbo].[sueetie_vw_UserProfileData]'

/* -- CREATE VIEW [dbo].[sueetie_vw_UserProfileData]  ------------------------------------------------------------------------------------------------------------------------------- */

/****** Object:  View [dbo].[sueetie_vw_UserProfileData]    Script Date: 03/20/2009 10:34:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[sueetie_vw_UserProfileData]
AS
SELECT     dbo.Sueetie_Users.UserID, dbo.Sueetie_Users.UserName, dbo.Sueetie_fn_GetProfileElement(N'DisplayName', dbo.aspnet_Profile.PropertyNames, 
                      dbo.aspnet_Profile.PropertyValuesString) AS DisplayName, dbo.Sueetie_fn_GetProfileElement(N'Gender', dbo.aspnet_Profile.PropertyNames, 
                      dbo.aspnet_Profile.PropertyValuesString) AS Gender, dbo.Sueetie_fn_GetProfileElement(N'Country', dbo.aspnet_Profile.PropertyNames, 
                      dbo.aspnet_Profile.PropertyValuesString) AS Country, dbo.Sueetie_fn_GetProfileElement(N'Occupation', dbo.aspnet_Profile.PropertyNames, 
                      dbo.aspnet_Profile.PropertyValuesString) AS Occupation, dbo.Sueetie_fn_GetProfileElement(N'Website', dbo.aspnet_Profile.PropertyNames, 
                      dbo.aspnet_Profile.PropertyValuesString) AS Website, dbo.Sueetie_fn_GetProfileElement(N'TwitterName', dbo.aspnet_Profile.PropertyNames, 
                      dbo.aspnet_Profile.PropertyValuesString) AS TwitterName
FROM         dbo.aspnet_Profile INNER JOIN
                      dbo.Sueetie_Users ON dbo.aspnet_Profile.UserId = dbo.Sueetie_Users.MembershipID

GO


print 'CREATE PROCEDURE [dbo].[sueetie_User_Create]'

/* -- CREATE PROCEDURE [dbo].[sueetie_User_Create]  ------------------------------------------------------------------------------------------------------------------------------- */



/****** Object:  StoredProcedure [dbo].[sueetie_User_Create]    Script Date: 03/20/2009 10:36:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sueetie_User_Create]
(
	@UserName nvarchar(50),
	@Email nvarchar(255),
	@MembershipID uniqueidentifier
)
AS
BEGIN


  insert into Sueetie_Users (MembershipID, UserName, Email) values (@MembershipID, @UserName, @Email)
  
  insert into Sueetie_UserAvatar (UserID) values (@@IDENTITY)

END

GO


print 'CREATE PROCEDURE [dbo].[sueetie_User_getByID]'

/* -- CREATE PROCEDURE [dbo].[sueetie_User_getByID]  ------------------------------------------------------------------------------------------------------------------------------- */



/****** Object:  StoredProcedure [dbo].[sueetie_User_getByID]    Script Date: 03/20/2009 10:37:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sueetie_User_getByID] 
(
	@userid int
)
AS
SET NOCOUNT, XACT_ABORT ON

SELECT     dbo.Sueetie_Users.UserID, dbo.Sueetie_Users.MembershipID, dbo.Sueetie_Users.UserName, dbo.Sueetie_Users.Email, 
                      dbo.Sueetie_UserAvatar.AvatarImage, dbo.Sueetie_UserAvatar.AvatarImageType
FROM         dbo.Sueetie_Users INNER JOIN
                      dbo.Sueetie_UserAvatar ON dbo.Sueetie_Users.UserID = dbo.Sueetie_UserAvatar.UserID INNER JOIN
                      dbo.aspnet_Users ON dbo.Sueetie_Users.MembershipID = dbo.aspnet_Users.UserId
           where Sueetie_Users.UserId = @userid
                      
RETURN

GO


print 'CREATE PROCEDURE [dbo].[sueetie_User_GetProfileDetails]'

/* -- CREATE PROCEDURE [dbo].[sueetie_User_GetProfileDetails]  ------------------------------------------------------------------------------------------------------------------------------- */


/****** Object:  StoredProcedure [dbo].[sueetie_User_GetProfileDetails]    Script Date: 03/20/2009 10:37:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sueetie_User_GetProfileDetails]

@userid int

AS
SELECT   
                     Prof.UserName,
                     Prof.DisplayName,
                     Prof.Gender,
                     Prof.Country,
                     Prof.Occupation, 
                     Prof.Website,
                     Prof.Twittername
                     

FROM sueetie_vw_UserProfileData Prof
WHERE Prof.userid = @userid

GO

print 'CREATE PROCEDURE [dbo].[sueetie_User_SaveAvatar]'

/* -- CREATE PROCEDURE [dbo].[sueetie_User_SaveAvatar] ------------------------------------------------------------------------------------------------------------------------------- */


/****** Object:  StoredProcedure [dbo].[sueetie_User_SaveAvatar]    Script Date: 03/20/2009 10:38:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sueetie_User_SaveAvatar]
(
	@UserID int,
	@AvatarImage image = NULL,
	@AvatarImageType nvarchar(50) = NULL
)
AS
BEGIN

		UPDATE
			[dbo].[Sueetie_UserAvatar]
		SET
			AvatarImage = @AvatarImage,
			AvatarImageType = @AvatarImageType
		WHERE
			UserID = @UserID
	END

GO

print 'POPULATE Sueetie_Users and Sueetie_UserAvatar with existing member data'

/* -- POPULATE Sueetie_Users and Sueetie_UserAvatar with existing member data ------------------------------------------------------------------------------------------------------------------------------- */


insert into Sueetie_Users (MembershipID, UserName, Email) 
	select userid, username, 'na@email.com' from aspnet_Users

update Sueetie_Users set Email = loweredEmail from aspnet_Membership m 
	inner join Sueetie_Users u on m.UserId = u.MembershipID
	where u.Email = 'na@email.com' and LoweredEmail is not null

insert into Sueetie_UserAvatar (UserID) select userid from Sueetie_Users


print 'Complete!'

